CREATE TABLE jms_dm.dm_tab_cn_center_scan_lack_agg_dt
(
    scan_code         varchar(100) COMMENT '操作中心编码',
    agent_code        varchar(100) COMMENT '操作中心代理区编码',
    mage_region_code  varchar(100) COMMENT '管理大区编码',
    order_source_code varchar(100) COMMENT '订单来源编码',
    scan_name         varchar(100) COMMENT '操作中心名称',
    agent_name        varchar(100) COMMENT '操作中心代理区名称',
    mage_region_name  varchar(100) COMMENT '管理大区名称',
    order_source_name varchar(100) COMMENT '订单来源名称',
    ought_scan_cnt    bigint(20) COMMENT '应扫描运单数量',
    arrival_lack_cnt  bigint(20) COMMENT '卸车到件漏扫数量',
    arrival_lack_rate double COMMENT '卸车到件漏扫数量',
    send_lack_cnt     bigint(20) COMMENT '装车发件漏扫数量',
    send_lack_rate    double COMMENT '装车发件漏扫数量',
    com_lack_rate     double COMMENT '综合漏扫率',
    date_time         date COMMENT '日期'
) ENGINE = OLAP DUPLICATE KEY(scan_code, agent_code, mage_region_code, order_source_code)
COMMENT '中心漏扫-汇总'
PARTITION BY RANGE(date_time)
(START ('2022-08-01') END ('2022-02-20') EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(scan_code) BUCKETS 1
PROPERTIES (
'replication_num' = '3',
'dynamic_partition.enable' = 'true',
'dynamic_partition.time_unit' = 'DAY',
'dynamic_partition.time_zone' = 'Asia/Shanghai',
'dynamic_partition.start' = '-180',
'dynamic_partition.end' = '3',
'dynamic_partition.prefix' = 'p',
'dynamic_partition.buckets' = '1',
'in_memory' = 'false',
'storage_format' = 'V2'
);